﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common;

namespace DAL
{
    /// <summary>
    /// 访问 access(Microsoft Access 2010) 数据类的通用类
    /// </summary>
    internal class OleDbHelper
    {
        //创建连接Excel字符串(不同版本不一样)
        static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0 ;Extended Properties=Excel 8.0;Data Source={0}";
        /// <summary>
        /// 更新操作
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="path">文件路径</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int Update(string sql, string path)
        {
            OleDbConnection conn = new OleDbConnection(string.Format(connStr, path));
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                 FrmLog_Com.WriteLogInfo(ex.Message);
                throw new Exception("OleDb更新数据异常:" + ex.Message);
            }

        }
        #region 执行单一结果查询

        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回单一结果</returns>
        public static object GetScalar(string sql, string path)
        {
            //
            //数据库连接对象
            OleDbConnection conn = new OleDbConnection(string.Format(connStr, path));
            //创建执行的SQL语句命令对象
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //异常写入日志
                 FrmLog_Com.WriteLogInfo(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region 获取datareader读取器
        /// <summary>
        /// 获取OleDbdatareader读取器
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回读取器datareader对象</returns>
        public static OleDbDataReader GetDataReader(string sql, string path)
        {
            //
            //数据库连接对象
            OleDbConnection conn = new OleDbConnection(string.Format(connStr, path));
            //创建执行的SQL语句命令对象
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            try
            {
                conn.Open();
                //关闭datareader时关闭数据库连接
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //异常写入日志
                 FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            //不能直接关闭
            //数据没有读取完不能关闭，关闭后无法读取

            //finally
            //{
            //    conn.Close();
            //}
        }
        #endregion
        #region 获取dataset数据集
        /// <summary>
        /// 获取dataset数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回数据集dataset对象</returns>
        public static DataSet GetDataset(string sql, string path)
        {
            //数据库连接对象
            OleDbConnection conn = new OleDbConnection(string.Format(connStr, path));
            //创建执行的SQL语句命令对象
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            //数据集对象    
            DataSet ds = new DataSet();
            //创建适配器对象
            OleDbDataAdapter OleDbData = new OleDbDataAdapter(cmd);
            try
            {
                conn.Open();
                //创建数据集对象（相当于前端虚拟的数据库，保存再内存中）
                OleDbData.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                //异常写入日志
                 FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 获取 OleDbdatatable数据表(内存中)
        /// <summary>
        /// 获取datatable数据表(内存中)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回datatable对象</returns>
        public static DataTable GetDateTime(string sql, string path)
        {
            //数据库连接对象
            OleDbConnection conn = new OleDbConnection(string.Format(connStr, path));
            //创建执行的SQL语句命令对象
            DataTable dt = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
            try
            {
                conn.Open();
                //关闭datareader时关闭数据库连接
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                //异常写入日志
                 FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            //不能直接关闭
            //数据没有读取完不能关闭，关闭后无法读取

            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region 获取dataview视图(内存中)
        /// <summary>
        /// 获取dataview视图(内存中)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回datatable对象</returns>
        public static DataView GetDatView(string sql, string path)
        {
            //数据库连接对象
            OleDbConnection conn = new OleDbConnection(string.Format(connStr, path));
            //创建执行的SQL语句命令对象
            DataTable dt = new DataTable();
            //创建数据视图对象
            DataView dataView = new DataView();
            //创建适配器对象
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
            try
            {
                conn.Open();
                //关闭datareader时关闭数据库连接
                adapter.Fill(dt);
                dataView.Table = dt;
                return dataView;
            }
            catch (Exception ex)
            {
                //异常写入日志
                 FrmLog_Com.WriteLogInfo(ex.Message);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            //不能直接关闭
            //数据没有读取完不能关闭，关闭后无法读取

            finally
            {
                conn.Close();
            }

        }
        #endregion
    }
}